---
title: Database
description: Build isolated, zero-setup databases using SQLite Durable Objects and SQL-based queries with full TypeScript support.
experimental: true
---

import { Aside, Code, LinkCard, Badge } from "@astrojs/starlight/components";

The SDK includes a built-in database solution using **SQLite Durable Objects** and **Kysely** for SQL queries. Create isolated databases at runtime with minimal setup.

---

## Motivation

We believe a lightweight, SQL-based query builder is the best fit as the out of the box solution. With just SQL, **you either already know it** (so you can be immediately productive) **or learning it is transferrable knowledge**. This doesn't replace your existing ORMs - you're always free to use your preferred database solution where it makes sense.

For applications with modular components or add-ons, there's an additional benefit: natural isolation. Each database instance is completely separate, giving you explicit control over how components communicate with each other's data.

`rwsdk/db` delivers both simplicity and isolation in one package: Write your migrations, call `createDb()`, and start querying with full type safety. Types are inferred directly from your migrations.

---

## How It Works

Under the hood, `rwsdk/db` combines:

1. **SQLite Durable Objects** - Each database instance runs in its own isolated Durable Object
2. **Kysely** - A lightweight, type-safe SQL query builder with the same API naming and semantics as SQL

### Type Inference

Instead of code generation or handwritten types, we infer your database schema directly from your migrations:

```ts
import { type Migrations } from "rwsdk/db";

export const migrations = {
  "001_initial_schema": {
    async up(db) {
      return [
        await db.schema
          .createTable("users")
          .addColumn("id", "text", (col) => col.primaryKey())
          .addColumn("username", "text", (col) => col.notNull().unique())
          .execute(),
      ];
    },
  },
} satisfies Migrations;

// TypeScript automatically knows about your 'users' table and its columns
const user = await db.selectFrom("users").selectAll().executeTakeFirst();
```

### When Migrations Run

Migrations run when `createDb()` is called. If that happens at the module level (shown in the examples), then:

**Development**:. Runs when you start your development server.

**Production**: When you deploy with `npm run release`, the deployment process includes an initial request to your application, which triggers migration updates.

### Migration Failures and Rollback

If a migration’s `up()` function fails, `rwsdk/db` automatically calls the corresponding `down()` function to undo any partial changes. This rollback is per-migration - previously successful ones are not affected.

Because SQLite doesn’t support transactional DDL (Data Definition Language) statements, a failed migration can leave the database in a partially modified state. It is therefore important to write `down()` functions that are idempotent and can run safely even if `up()` only partially succeeded.

```ts
// Example of a defensive down() function
async down(db) {
  // Defensively drop tables that might not exist if `up()` failed
  await db.schema.dropTable("posts").ifExists().execute();
  await db.schema.dropTable("users").ifExists().execute();
}
```

---

## Setup

You'll need to create three files and update your Wrangler configuration:

### 1. Define Your Migrations

```ts title="src/db/migrations.ts"
import { type Migrations } from "rwsdk/db";

export const migrations = {
  "001_initial_schema": {
    async up(db) {
      return [
        await db.schema
          .createTable("todos")
          .addColumn("id", "text", (col) => col.primaryKey())
          .addColumn("text", "text", (col) => col.notNull())
          .addColumn("completed", "integer", (col) =>
            col.notNull().defaultTo(0),
          )
          .addColumn("createdAt", "text", (col) => col.notNull())
          .execute(),
      ];
    },

    async down(db) {
      await db.schema.dropTable("todos").ifExists().execute();
    },
  },
} satisfies Migrations;
```

### 2. Create Your Database Instance

```ts title="src/db/index.ts"
import { env } from "cloudflare:workers";
import { type Database, createDb } from "rwsdk/db";
import { type migrations } from "@/db/migrations";

export type AppDatabase = Database<typeof migrations>;
export type Todo = AppDatabase["todos"];

export const db = createDb<AppDatabase>(
  env.DATABASE,
  "todo-database", // unique key for this database instance
);
```

### 3. Create Your Durable Object Class

```ts title="src/db/durableObject.ts"
import { SqliteDurableObject } from "rwsdk/db";
import { migrations } from "@/db/migrations";

export class Database extends SqliteDurableObject {
  migrations = migrations;
}
```

### 4. Export from Worker

```ts title="src/worker.tsx"
export { Database } from "@/db/durableObject";

// ... rest of your worker code
```

### 5. Configure Wrangler

```jsonc title="wrangler.jsonc"
{
  "durable_objects": {
    "bindings": [
      {
        "name": "DATABASE",
        "class_name": "Database",
      },
    ],
  },
  "migrations": [
    {
      "tag": "v1",
      "new_sqlite_classes": ["Database"],
    },
  ],
}
```

After updating `wrangler.jsonc`, run `pnpm generate` to update the generated type definitions.

Ensure `src/db/index.ts`, the Durable Object export in `src/worker.tsx`, and the Wrangler configuration all refer to the same binding and class names. The examples use `Database`.

---

## Usage Examples

### Basic CRUD Operations

```ts
import { db } from "@/db";

// Create a todo
const todo = {
  id: crypto.randomUUID(),
  text: "Finish the documentation",
  completed: 0,
  createdAt: new Date().toISOString(),
};
await db.insertInto("todos").values(todo).execute();

// Find a todo
const foundTodo = await db
  .selectFrom("todos")
  .selectAll()
  .where("id", "=", todo.id)
  .executeTakeFirst();

// Update a todo
await db
  .updateTable("todos")
  .set({ completed: 1 })
  .where("id", "=", todo.id)
  .execute();

// Delete a todo
await db.deleteFrom("todos").where("id", "=", todo.id).execute();
```

### Complex Queries with Joins

While the guestbook example is simple, you can still perform joins. For a more detailed example, see the **Patterns** section below.

### Real-World Example: Passkey Authentication

Here's how the [passkey addon](https://github.com/redwoodjs/passkey-addon) uses `rwsdk/db`:

```ts
// Create a new credential
export async function createCredential(
  credential: Omit<Credential, "id" | "createdAt">,
): Promise<Credential> {
  const newCredential: Credential = {
    id: crypto.randomUUID(),
    createdAt: new Date().toISOString(),
    ...credential,
  };

  await db.insertInto("credentials").values(newCredential).execute();
  return newCredential;
}

// Find credentials for a user
export async function getUserCredentials(
  userId: string,
): Promise<Credential[]> {
  return await db
    .selectFrom("credentials")
    .selectAll()
    .where("userId", "=", userId)
    .execute();
}
```

---

## Patterns

### Nesting Relational Data (ORM-like Behavior)

While `rwsdk/db` uses a query builder instead of a full ORM, you can still structure your query results to include nested relational data. Kysely provides helper functions like `jsonObjectFrom` and `jsonArrayFrom` that make this easy.

For this example, we'll switch to a more complex schema involving blog posts and users to better demonstrate joins.

**1. The Schema**

First, let's assume a schema with `users` and `posts`.

```ts title="src/db/migrations.ts"
// Abridged for clarity
await db.schema
  .createTable("users")
  .addColumn("id", "text", (col) => col.primaryKey())
  .addColumn("username", "text", (col) => col.notNull().unique())
  .execute();

await db.schema
  .createTable("posts")
  .addColumn("id", "text", (col) => col.primaryKey())
  .addColumn("title", "text", (col) => col.notNull())
  .addColumn("userId", "text", (col) => col.notNull().references("users.id"))
  .execute();
```

**2. The Query**

With the schema in place, you can write a query to fetch posts and embed the author's information.

```ts title="src/db/queries.ts"
import { db } from "@/db";
import { jsonObjectFrom } from "kysely/helpers/sqlite";

export async function getAllPostsWithAuthors() {
  return await db
    .selectFrom("posts")
    .selectAll("posts")
    .select((eb) => [
      jsonObjectFrom(
        eb
          .selectFrom("users")
          .select(["id", "username"])
          .whereRef("users.id", "=", "posts.userId"),
      ).as("author"),
    ])
    .execute();
}
```

**3. The Result**

The `getAllPostsWithAuthors` function will return an array of post objects, each with a nested author object:

```json
[
  {
    "id": "post-123",
    "title": "My First Post",
    "author": { "id": "user-abc", "username": "Alice" }
  }
]
```

This pattern allows you to fetch complex, nested data structures in a single, efficient query.

---

## Seeding Your Database

For development and testing, you'll often need a consistent set of data. You can create a seed script to populate your database with default values.

### 1. Create a Seed Script

Create a script that exports an async function as the default export. This script will have access to your application's environment, including your Durable Object bindings, when run via `rwsdk worker-run`.

```ts title="src/scripts/seed.ts"
import { db } from "@/db";

export default async () => {
  console.log("… Seeding todos");
  await db.deleteFrom("todos").execute();

  await db
    .insertInto("todos")
    .values([
      {
        id: crypto.randomUUID(),
        text: "Write the seed script",
        completed: 1,
        createdAt: new Date().toISOString(),
      },
      {
        id: crypto.randomUUID(),
        text: "Update the documentation",
        completed: 0,
        createdAt: new Date().toISOString(),
      },
    ])
    .execute();

  console.log("✔ Finished seeding todos 🌱");
};
```

### 2. Add a `seed` script to `package.json`

Add a script to your `package.json` to run your seed file using the `rwsdk worker-run` command.

```json title="package.json"
{
  "scripts": {
    "seed": "rwsdk worker-run ./src/scripts/seed.ts"
  }
}
```

### 3. Run the Seed Script

Now you can seed your database from the command line:

```bash
npm run seed
```

---

## API Reference

### `createDb()`

Creates a database instance connected to a Durable Object.

```ts
createDb<T>(durableObjectNamespace: DurableObjectNamespace, key: string): Database<T>
```

- `durableObjectNamespace`: Your Durable Object binding from the environment
- `key`: Unique identifier for this database instance
- Returns: Kysely database instance with your inferred types

### `Database<T>` Type

The main database type that provides access to your tables and their schemas.

```ts
type AppDatabase = Database<typeof migrations>;
type Todo = AppDatabase["todos"]; // Inferred table type
```

### `Migrations` Type

Use to define the structure for your database migrations.

```ts
export const migrations = {
  "001_create_todos": {
    async up(db) {
      await db.schema
        .createTable("todos")
        .addColumn("id", "text", (col) => col.primaryKey())
        .addColumn("text", "text", (col) => col.notNull())
        .addColumn("completed", "integer", (col) => col.notNull().defaultTo(0))
        .execute();
    },
    async down(db) {
      await db.schema.dropTable("todos").execute();
    },
  },
} satisfies Migrations;
```

### `SqliteDurableObject`

Base class for your Durable Object that handles SQLite operations.

```ts
class YourDurableObject extends SqliteDurableObject {
  migrations = yourMigrations;
}
```

For complete query builder documentation, see the [Kysely documentation](https://kysely.dev/docs). Everything you can do with Kysely, you can do with `rwsdk/db`.

---

## FAQ

**Q: Why use SQL instead of an ORM?**

A: We're not replacing ORMs - `rwsdk/db` works alongside your existing tools. We believe a lightweight, SQL-based query builder is a better fit as the out of the box solution, but you're always free to use your preferred ORM or database solution where it makes sense for your application.

**Q: What about latency and performance?**

A: Durable Objects run in a single location, so there's a latency consideration compared to globally distributed databases. However, they excel at simplicity and isolation. For many use cases, the ease of setup benefit outweighs the latency trade-off. You can also create multiple database instances with different keys to distribute load geographically if needed.

**Q: Is this suitable for production use?**

A: This is currently a preview feature, which means the API may evolve based on feedback. The underlying technologies (SQLite, Durable Objects, Kysely) are all production-ready, but we recommend testing thoroughly and having migration strategies ready as the API stabilizes.

**Q: How do I handle database backups?**

A: Durable Objects automatically persist data, but like D1, there aren't built-in backup features. For critical applications, implement additional backup strategies. You can export data periodically or replicate to external systems as needed.

**Q: Why does rwsdk/db auto-rollback failed migrations instead of leaving recovery to the developer?**

A: We recognize that in many scenarios, particularly in production, a developer is best equipped to handle a failed migration with full context. Manual recovery can offer more granular control than a one-size-fits-all automated approach.

However, `rwsdk/db` opts for automated rollbacks by default to ensure database integrity. The primary reason is that SQLite does not support transactions for schema changes (DDL). A failed `up()` migration could otherwise leave the database in an inconsistent, half-migrated state. By automatically running the `down()` function, we return the database to a known-good state. This is critical for the zero-setup, runtime-isolated environments `rwsdk/db` is designed for, where direct manual intervention may not be feasible.

To work effectively with this automated system, it's best to plan migrations carefully. Each `down()` function should be written to cleanly undo only what its corresponding `up()` function does. This practice makes it much easier and safer to reason about the database state, fix the migration, and redeploy.
